﻿/*
Copyright (C) 2009  Tomasz Chrzanowski

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>
*/

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Win32;
using System.Data.SqlClient;
using System.Collections;
using System.Text.RegularExpressions;

namespace Com.TomCdc.ChangeDataCapture
{
    /// <summary>
    /// Container for all globaly used constant values.
    /// </summary>
    public static class CommonHelper
    {
        public static readonly DateTime ExpiryDate = DateTime.MaxValue;

        /// <summary>
        /// The list of sql data types which can not be tracked.
        /// </summary>
        private static readonly CaseInsensitiveList _notTrackableTypes =
            new CaseInsensitiveList(new string[] { "text", "ntext", "image", "timestamp", "xml", "binary", "varbinary", "cursor", "hierarchyid", "table" });

        /// <summary>
        /// The token used to mark start and end of the list of columns in capturing trigger.
        /// </summary>
        public const string TRIGGER_COL_LIST_TOKEN = "/*COLUMNLIST*/";

        /// <summary>
        /// The cdc agent sevice name.
        /// </summary>
        public const string CDC_AGENT_SERVICE_NAME = "CdcProcessorAgent";

        /// <summary>
        /// The prefix of store procedure used as a source record IDs selector.
        /// </summary>
        public const string SELECTOR_SP_PREFIX = "spCDC_SELECTOR__";

        /// <summary>
        /// The separator expresion between schema and table name to produce table identifier.
        /// </summary>
        public const string SCHEMA_TABLE_SEPARATOR = "$";

        /// <summary>
        /// The list of sql data types allowed for table primary key or uniqe column.
        /// </summary>
        /// <returns>The string array of sql data types.</returns>
        public static CaseInsensitiveList AllowedPrimaryKeyTypes
        {
            get
            {
                return _allowedPrimaryKeyType;
            }
        }
        private static readonly CaseInsensitiveList _allowedPrimaryKeyType =
            new CaseInsensitiveList(new string[] { "tinyint", "smallint", "int", "bigint" });

        private static string SQL_GET_EXT_PROPERTY = @"SELECT value FROM
            ::fn_listextendedproperty('CdcDbType', NULL, NULL, NULL, NULL, default, default)";

        private static string SQL_GET_SERVER_VERSION = @"SELECT SERVERPROPERTY('productversion') ProductVersion";

        /// <summary>
        /// Gets the list of sql data types which can not be tracked.
        /// </summary>
        /// <returns>The string array of sql data types.</returns>
        public static CaseInsensitiveList NotTrackableTypes
        {
            get
            {
                return _notTrackableTypes;
            }
        }

        /// <summary>
        /// The key under which application data will be stored.
        /// </summary>
        public const string REGISTRY_APP_KEY = "ChangeDataCapture";

        /// <summary>
        /// Retrievs <see cref="RegistryKey"/> object representing subkey passed in parameter.
        /// The parameter subkey is relative to application key.
        /// </summary>
        /// <param name="appRelativeSubKey">Subkey in application key.</param>
        /// <returns></returns>
        public static RegistryKey GetRegUserKey(string appRelativeSubKey, bool writable)
        {
            if (appRelativeSubKey == null)
            {
                throw new ArgumentNullException("appRelativeSubKey");
            }

            string path = "Software\\" + REGISTRY_APP_KEY +
                (appRelativeSubKey.Length > 0 ? "\\" + appRelativeSubKey : "");

            RegistryKey regKey = Registry.CurrentUser.OpenSubKey(path, writable);
            if (regKey == null)
            {
                regKey = Registry.CurrentUser.CreateSubKey(path);
            }
            return regKey;
        }

        /// <summary>
        /// Formats string by inserting space before each upper case character.
        /// </summary>
        /// <param name="s">String to be formated.</param>
        /// <returns>Formated string with spaces separating words.</returns>
        public static string FormatPascalString(string s)
        {
            StringBuilder result = new StringBuilder(s.Length * 2);

            foreach (char c in s)
            {
                if (Char.IsUpper(c) && result.Length > 0) result.Append(' ');
                result.Append(c);
            }
            return result.ToString();
        }


        /// <summary>
        /// Checks if the database referenced in connectionString is of the type passed in expectedType argument.
        /// </summary>
        /// <param name="connectionString">Sql connection string to database to be checked.</param>
        /// <param name="expectedType">Expected type of database referenced in connectionString argument.</param>
        /// <returns>True if the database is of expected type, otherwise false.</returns>
        public static bool IsDatabaseOfCdcType(string connectionString, CdcDatabaseType expectedType)
        {
            string CdcDbType;
            try
            {
                // retrieves extended property value definied on ChangeTransaction table
                using (SqlConnection sqlConn = new SqlConnection(connectionString))
                using (SqlCommand sqlCmd = new SqlCommand(SQL_GET_EXT_PROPERTY, sqlConn))
                {
                    sqlConn.Open();
                    CdcDbType = (string)sqlCmd.ExecuteScalar();
                }
            }
            catch
            {
                return false;
            }

            switch (expectedType)
            {
                case CdcDatabaseType.Tracked:
                    if (CdcDbType == "CdcTracked") return true;
                    break;
                case CdcDatabaseType.Staging:
                    if (CdcDbType == "CdcStaging") return true;
                    break;
                case CdcDatabaseType.Storage:
                    if (CdcDbType == "CdcStorage") return true;
                    break;
            }

            return false;
        }

        public static CdcDatabaseType IdentifyDatabaseType(string connectionString)
        {
            string CdcDbType;
            try
            {
                // retrieves extended property value definied on ChangeTransaction table
                using (SqlConnection sqlConn = new SqlConnection(connectionString))
                using (SqlCommand sqlCmd = new SqlCommand(SQL_GET_EXT_PROPERTY, sqlConn))
                {
                    sqlConn.Open();
                    CdcDbType = (string)sqlCmd.ExecuteScalar();
                }
            }
            catch
            {
                return CdcDatabaseType.Undefined;
            }

            switch (CdcDbType)
            {
                case "CdcTracked":
                    return CdcDatabaseType.Tracked;
                case "CdcStaging":
                    return CdcDatabaseType.Staging;
                case "CdcStorage":
                    return CdcDatabaseType.Storage;
                default:
                    return CdcDatabaseType.Undefined;
            }
        }

        public static SqlServerVersion GetServerVersion(string connectionString)
        {
            using (SqlConnection sqlCon = new SqlConnection(connectionString))
            using (SqlCommand sqlCmd = new SqlCommand(SQL_GET_SERVER_VERSION, sqlCon))
            {
                sqlCon.Open();
                string res = (string)sqlCmd.ExecuteScalar();
                if (res.StartsWith("8.")) return SqlServerVersion.Sql2000;
                if (res.StartsWith("9.")) return SqlServerVersion.Sql2005;
                if (res.StartsWith("10.")) return SqlServerVersion.Sql2008;
                throw new InvalidOperationException("Not supported sql server version.");
            }
        }

        /// <summary>
        /// Converts <see cref="BitArray"/> to hexadecimal representation and returns the result as a characters string.
        /// The result is prefixed with "0x" characters.
        /// </summary>
        /// <param name="bitArray">The set of bits to convert.</param>
        /// <returns>The hexadecimal representation of <paramref name="bitArray"/>.</returns>
        public static string ConvertToHexadecimal(BitArray bitArray)
        {
            // prepares 8 bit long string
            StringBuilder eightBit = new StringBuilder(8);

            // prepares string for result
            StringBuilder resultBitMask = new StringBuilder("0x", bitArray.Length / 4 + 1);

            // goes through all bits in the bit array representing all table columns
            for (int colIdx = 0; colIdx < bitArray.Length; colIdx++)
            {
                // adds 1 when bit set, otherwise 0
                eightBit.Insert(0, bitArray[colIdx] ? "1" : "0");

                // when 8 bits collected calcultes hex value
                if ((colIdx + 1) % 8 == 0)
                {
                    // converts eightBit from binary to decimal number, then decimal number to hexadecimal
                    // (  {0:X2} - converts to hex number prefixes with 0 if necessary)
                    resultBitMask.Append(string.Format("{0:X2}", Convert.ToByte(eightBit.ToString(), 2)));

                    // clear eightBit variable, so ready for next byte
                    eightBit.Length = 0;
                }
            }

            // process if anything left
            if (eightBit.Length > 0)
            {
                resultBitMask.Append(string.Format("{0:X2}", Convert.ToByte(eightBit.ToString(), 2)));
            }

            // returns bit mask string
            return resultBitMask.ToString();
        }

        /// <summary>
        /// Converts the hexadecimal characters string to <see cref="BitArray"/> object.
        /// </summary>
        /// <param name="hexString">The hexadecimal string starting with 0x and having even number of characters.</param>
        /// <returns>The <see cref="BitArray"/> object representing an argument <paramref name="hexString"/> bit set.</returns>
        public static BitArray ConvertToBitArray(string hexString)
        {
            if (!hexString.StartsWith("0x"))
            {
                throw new ArgumentException("The hexString argument must start with 0x characters.");
            }

            if (hexString.Length % 2 != 0)
            {
                throw new ArgumentException("The number of characters in the hexString argument must be even.");
            }

            Byte[] subRes = new Byte[hexString.Length / 2 - 1];
            for (int i = 1; i < hexString.Length / 2; i++)
            {
                subRes[i - 1] = Convert.ToByte(hexString.Substring(i * 2, 2), 16);
            }

            return new BitArray(subRes);
        }

        /// <summary>
        /// Splits the sql script into the array of sql batches.
        /// </summary>
        /// <param name="script">The script to split.</param>
        /// <returns>The array of batches.</returns>
        public static string[] SplitSqlBatches(string script)
        {
            List<string> batches = new List<string>();
            Regex batchMarker = new Regex(@"\s*GO\s*", RegexOptions.IgnoreCase);
            StringBuilder sb = new StringBuilder();
            string[] scriptLines = script.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string line in scriptLines)
            {
                if (batchMarker.IsMatch(line))
                {
                    batches.Add(sb.ToString());
                    sb.Length = 0;
                }
                else
                {
                    sb.Append(line + Environment.NewLine);
                }
            }
            if (sb.Length > 0)
            {
                batches.Add(sb.ToString());
            }
            return batches.ToArray();
        }

        /// <summary>
        /// Executes batch from the provided array one at a time against database.
        /// </summary>
        /// <param name="batches">A list of batches to execute.</param>
        /// <param name="sqlCmd">A Sql Command used to execute the batch.</param>
        public static void ExecuteBatchArray(string[] batches, SqlCommand sqlCmd)
        {
            foreach (string batch in batches)
            {
                sqlCmd.CommandText = batch;
                sqlCmd.ExecuteNonQuery();
            }
        }
    }
}
